from flask import Flask, request, jsonify
import pymysql
from pymysql.cursors import DictCursor
from app import logger
from db_config import DB_CONFIG
from sqls import SQLS
from typing import cast
from werkzeug.datastructures import MultiDict
import sys
import decimal
import datetime
from flask_cors import CORS
import json

app = Flask(__name__)
CORS(app)

@app.route('/api/test', methods=['GET'])
def test_api():
    """
    测试接口，用于验证API服务器是否正常运行
    """
    return jsonify({
        'code': 200,
        'message': 'API服务器运行正常',
        'timestamp': str(datetime.datetime.now())
    })

def get_db_connection():
    return pymysql.connect(
        host=DB_CONFIG['host'],
        port=DB_CONFIG.get('port', 3306),
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        db=DB_CONFIG.get('database') or DB_CONFIG.get('db'),
        charset=DB_CONFIG.get('charset', 'utf8mb4'),
        cursorclass=DictCursor
    )

def decimal_to_float(obj):
    if isinstance(obj, list):
        return [decimal_to_float(i) for i in obj]
    elif isinstance(obj, dict):
        return {k: decimal_to_float(v) for k, v in obj.items()}
    elif isinstance(obj, decimal.Decimal):
        return float(obj)
    else:
        return obj

def print_sql_log(tag, sql, sql_params, extra=None):
    if extra:
        print(f'[{tag}] {extra}', file=sys.stderr)
    print(f'[{tag}] SQL:', sql, file=sys.stderr)
    print(f'[{tag}] SQL params:', sql_params, file=sys.stderr)
    try:
        full_sql = sql
        for p in sql_params:
            rep = f"'{p}'" if p is not None else 'NULL'
            full_sql = full_sql.replace('%s', rep, 1)
        print(f'[{tag}] Full SQL:', full_sql, file=sys.stderr)
    except Exception as e:
        print(f'[{tag}] Full SQL error: {e}', file=sys.stderr)

@app.route('/api/shipments/weekly_analysis', methods=['GET'])
def weekly_analysis():
    """
    周度分析接口
    参数：
        department: 部门名，可为空
        seller: 销售人员名，可为空
    功能：
        查询前5个周的计费重、CBM、客户数，按pt_week升序排序，并将pt_week转为"第一周"~"第五周"
    返回：
        {
            "code": 200,
            "message": "success",
            "data": [
                {"pt_week": "第一周", "total_chargeable_weight": ..., "total_cbm": ..., "customer_count": ...},
                ...
            ]
        }
    特殊处理：
        - 排除status='cancelled'的数据
        - pt_week字段自动转为"第一周"~"第五周"
    """
    try:
        logger.info("=== 周度分析接口调用开始 ===")
        
        args = cast(MultiDict, request.args)  # type: ignore
        department = args.get('department', default=None, type=str)
        seller = args.get('seller', default=None, type=str)
        
        logger.info(f"接收到的参数: department={department}, seller={seller}")
        
        # 检查SQL是否存在
        if 'shipments_weekly_analysis' not in SQLS:
            logger.error("SQL配置中缺少 'shipments_weekly_analysis'")
            return jsonify({
                'code': 500,
                'message': 'SQL配置错误',
                'data': []
            }), 500
        
        sql = SQLS['shipments_weekly_analysis']
        logger.info(f"使用的SQL: {sql}")
        
        conn = get_db_connection()
        logger.info("数据库连接成功")
        
        try:
            with conn.cursor() as cursor:
                sql_params = (department, department, department, seller, seller, seller)
                logger.info(f"SQL参数: {sql_params}")
                
                print_sql_log('weekly_analysis', sql, sql_params, f'department={department}, seller={seller}')
                
                cursor.execute(sql, sql_params)
                result = cursor.fetchall()
                
                logger.info(f"数据库查询结果: {result}")
                logger.info(f"查询结果数量: {len(result) if result else 0}")
                
                if not result:
                    logger.warning("查询结果为空")
                    return jsonify({
                        'code': 200,
                        'message': 'success',
                        'data': []
                    })
                
                # 按pt_week升序排序
                result_sorted = sorted(result, key=lambda x: x['pt_week'])
                logger.info(f"排序后的结果: {result_sorted}")
                
                # 替换pt_week为"第一周"~"第五周"
                week_names = ['第一周', '第二周', '第三周', '第四周', '第五周']
                for i, row in enumerate(result_sorted):
                    row['pt_week'] = week_names[i] if i < len(week_names) else f'第{i+1}周'
                
                logger.info(f"处理后的最终结果: {result_sorted}")
                
                # 转换decimal类型为float
                final_result = decimal_to_float(result_sorted)
                logger.info(f"转换后的结果: {final_result}")
                
                response_data = {
                    'code': 200,
                    'message': 'success',
                    'data': final_result
                }
                
                logger.info(f"返回响应: {response_data}")
                logger.info("=== 周度分析接口调用成功 ===")
                
                return jsonify(response_data)
                
        finally:
            conn.close()
            logger.info("数据库连接已关闭")
            
    except Exception as e:
        logger.error(f"=== 周度分析接口调用失败 ===")
        logger.error(f"错误类型: {type(e).__name__}")
        logger.error(f"错误信息: {str(e)}")
        logger.error(f"错误详情: ", exc_info=True)
        
        return jsonify({
            'code': 500,
            'message': f'服务器内部错误: {str(e)}',
            'data': []
        }), 500

@app.route('/api/shipments/monthly_analysis', methods=['GET'])
def monthly_analysis():
    """
    月度分析接口
    参数：
        start_month: 起始月，两位字符串（如'01'）
        end_month: 结束月，两位字符串（如'06'）
        department: 部门名，可为空
        seller: 销售人员名，可为空
    功能：
        查询所选月份范围内每月的计费重、CBM、客户数，pt_month转为"X月"格式
    返回：
        [
            {"pt_month": "1月", "total_chargeable_weight": ..., "total_cbm": ..., "customer_count": ...},
            ...
        ]
    特殊处理：
        - 排除status='cancelled'的数据
        - pt_month字段自动转为"X月"格式
    """
    args = cast(MultiDict, request.args)  # type: ignore
    start_month = args.get('start_month', default=None, type=str)
    end_month = args.get('end_month', default=None, type=str)
    department = args.get('department', default=None, type=str)
    seller = args.get('seller', default=None, type=str)
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = SQLS['shipments_monthly_analysis']
            sql_params = (start_month, end_month, department, department, department, seller, seller, seller)
            print_sql_log('monthly_analysis', sql, sql_params, f'start_month={start_month}, end_month={end_month}, department={department}, seller={seller}')
            cursor.execute(sql, sql_params)
            result = cursor.fetchall()
        # pt_month转为'X月'格式
        for row in result:
            try:
                row['pt_month'] = f"{int(row['pt_month'])}月"
            except Exception:
                pass
        return jsonify({'data': decimal_to_float(result)})
    finally:
        conn.close()

@app.route('/api/shipments/risk_alerts_weekly', methods=['GET'])
def risk_alerts_weekly():
    """
    风险预警模块接口
    功能：
        以周为维度，统计前5周，按客户汇总计费重，返回客户、前4周、前3周、前2周、本周业绩、上周业绩、同比率（本周/上周）
        支持部门（department）、销售人员（seller）过滤，自动排除status='cancelled'的数据
    返回：
        [
            {"customer": ..., "week_4": ..., "week_3": ..., "week_2": ..., "last_week": ..., "this_week": ..., "ratio": ...},
            ...
        ]
    特殊处理：
        - pt_week按数字倒序取前5周
        - ratio为本周/上周，保留两位小数
    """
    args = cast(MultiDict, request.args)  # type: ignore
    department = args.get('department', default=None, type=str)
    seller = args.get('seller', default=None, type=str)
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            # 获取前5周的周号，按数字升序排列
            cursor.execute("SELECT DISTINCT pt_week FROM shipments_data WHERE status != 'cancelled' ORDER BY CAST(pt_week AS UNSIGNED) DESC LIMIT 5")
            weeks = [row['pt_week'] for row in cursor.fetchall()][::-1]
            # 动态设置变量
            if len(weeks) < 5:
                # 补齐5周，防止SQL报错
                weeks = ['0'] * (5 - len(weeks)) + weeks
            w1, w2, w3, w4, w5 = weeks
            # 设置MySQL变量
            cursor.execute(f"SET @w1='{w1}', @w2='{w2}', @w3='{w3}', @w4='{w4}', @w5='{w5}'")
            sql = SQLS['shipments_risk_alerts_weekly']
            sql_params = (department, department, department, seller, seller, seller)
            print_sql_log('risk_alerts_weekly', sql, sql_params, f'department={department}, seller={seller}, weeks={weeks}')
            cursor.execute(sql, sql_params)
            result = cursor.fetchall()
        return jsonify({'data': decimal_to_float(result)})
    finally:
        conn.close()

@app.route('/api/shipments/customer_ranking', methods=['GET'])
def customer_ranking():
    """
    客户排名模块接口
    参数：
        department: 部门名，可为空
        seller: 销售人员名，可为空
    功能：
        以客户维度，统计每个客户的计费重、实际重量、CBM、泡比，按计费重降序
        支持部门、销售人员过滤，自动排除status='cancelled'的数据
    返回：
        [
            {"username": ..., "total_chargeable_weight": ..., "total_actual_weight": ..., "total_cbm": ..., "volume_weight_radix": ...},
            ...
        ]
    """
    args = cast(MultiDict, request.args)  # type: ignore
    department = args.get('department', default=None, type=str)
    seller = args.get('seller', default=None, type=str)
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = SQLS['shipments_customer_ranking']
            sql_params = (department, department, department, seller, seller, seller)
            print_sql_log('customer_ranking', sql, sql_params, f'department={department}, seller={seller}')
            cursor.execute(sql, sql_params)
            result = cursor.fetchall()
        return jsonify({'data': decimal_to_float(result)})
    finally:
        conn.close()

@app.route('/api/shipments/sales_kpi', methods=['GET'])
def sales_kpi():
    """
    销售考核完成情况模块接口
    参数：
        department: 部门名，可为空
        seller: 销售人员名，可为空
    功能：
        以销售人员维度，统计2025年每月计费重，关联salesperson表，返回销售人员、1-12月计费重、2024年销售额、2025年新增目标、2025总目标、完成率
        支持部门、销售人员过滤，自动排除status='cancelled'的数据
    返回：
        [
            {"salesperson_name": ..., "m1": ..., ..., "m12": ..., "2024sales_volume": ..., "2025additional_new": ..., "2025total_target": ..., "finish_rate": ...},
            ...
        ]
    """
    args = cast(MultiDict, request.args)  # type: ignore
    department = args.get('department', default=None, type=str)
    seller = args.get('seller', default=None, type=str)
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = SQLS['shipments_sales_kpi']
            sql_params = (department, department, seller, seller)
            print_sql_log('sales_kpi', sql, sql_params, f'department={department}, seller={seller}')
            cursor.execute(sql, sql_params)
            result = cursor.fetchall()
        return jsonify({'data': decimal_to_float(result)})
    finally:
        conn.close()

@app.route('/api/shipments/channel_ratio', methods=['GET'])
def channel_ratio():
    """
    渠道占比模块接口
    参数：
        department: 部门名，可为空
        seller: 销售人员名，可为空
    功能：
        以服务渠道维度，统计每个服务渠道的计费重总数，支持部门、销售人员过滤，自动排除status='cancelled'的数据
    返回：
        [
            {"servicer_channel": ..., "total_chargeable_weight": ...},
            ...
        ]
    """
    args = cast(MultiDict, request.args)  # type: ignore
    department = args.get('department', default=None, type=str)
    seller = args.get('seller', default=None, type=str)
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = SQLS['shipments_channel_ratio']
            sql_params = (department, department, department, seller, seller, seller)
            print_sql_log('channel_ratio', sql, sql_params, f'department={department}, seller={seller}')
            cursor.execute(sql, sql_params)
            result = cursor.fetchall()
        return jsonify({'data': decimal_to_float(result)})
    finally:
        conn.close()

@app.route('/api/shipments/performance_data', methods=['GET'])
def performance_data():
    """
    业绩数据模块接口
    参数：
        department: 部门名，可为空
        seller: 销售人员名，可为空
    功能：
        自动获取本周、上周、本月、上月、本年，分别统计计费重、CBM、客户数，支持部门、销售人员过滤，自动排除status='cancelled'的数据
    返回：
        {
            weight_this_week, weight_last_week, weight_this_month, weight_last_month, weight_this_year,
            cbm_this_week, cbm_last_week, cbm_this_month, cbm_last_month, cbm_this_year,
            customer_this_week, customer_last_week, customer_this_month, customer_last_month, customer_this_year
        }
    """
    import datetime
    from calendar import monthrange
    args = cast(MultiDict, request.args)  # type: ignore
    department = args.get('department', default=None, type=str)
    seller = args.get('seller', default=None, type=str)

    today = datetime.date.today()
    this_year = today.strftime('%Y')
    this_month = today.strftime('%m')
    if today.month == 1:
        last_month = '12'
        last_month_year = str(today.year - 1)
    else:
        last_month = f'{today.month-1:02d}'
        last_month_year = this_year
    iso_calendar = today.isocalendar()
    this_week = f'{iso_calendar[1]:02d}'
    last_week_date = today - datetime.timedelta(days=7)
    last_week = f'{last_week_date.isocalendar()[1]:02d}'

    # 日志输出所有参数
    print('[performance_data] department:', department, 'seller:', seller, file=sys.stderr)
    print('[performance_data] this_week:', this_week, 'last_week:', last_week, 'this_month:', this_month, 'last_month:', last_month, 'this_year:', this_year, file=sys.stderr)

    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = SQLS['shipments_performance_data']
            sql_params = (
                this_week, last_week, this_month, last_month, this_year,
                this_week, last_week, this_month, last_month, this_year,
                this_week, last_week, this_month, last_month, this_year,
                department, department, department, seller, seller, seller
            )
            print_sql_log('performance_data', sql, sql_params, f'department={department}, seller={seller}, this_week={this_week}, last_week={last_week}, this_month={this_month}, last_month={last_month}, this_year={this_year}')
            cursor.execute(sql, sql_params)
            result = cursor.fetchone() or {}
        return jsonify({'data': decimal_to_float(result)})
    finally:
        conn.close()

@app.route('/api/shipments/active_salespersons', methods=['GET'])
def active_salespersons():
    """
    获取在职销售人员，按部门分组，返回格式：
    { '部门名称': [ {value: '销售人员', label: '销售人员'} ] }
    """
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = SQLS['active_salespersons_by_department']
            cursor.execute(sql)
            data = cursor.fetchall()
        result = {}
        for row in data:
            dep = row.get('department_name') or '未知部门'
            staff_json = row.get('staff')
            try:
                staff_list = json.loads(staff_json) if staff_json else []
            except Exception as e:
                staff_list = []
            result[dep] = staff_list
        return jsonify(result)
    finally:
        conn.close()

if __name__ == '__main__':
    logger.info("启动Flask服务...")
    app.run(host='0.0.0.0', port=5001, debug=True)